package com.itheima.health.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface ReportMapper extends BaseMapper {

    @Select("SELECT  count(tm.ID) as memberCounts,  " +
            "concat(year(rp.fdate),\"-\",rp.fmonth) as yearAndMonth " +
            "from rpt_date  rp  ,t_member  tm " +
            "where rp.fdate = tm.REGTIME " +
            "and rp.fyear = year(now())-1 " +
            "group by rp.fmonth")
    List<Map> findLastyearMemberCountsByMonth();


    @Select("SELECT s. NAME as name, count(o.setmeal_id) as value " +
            "FROM t_order o INNER JOIN t_setmeal s " +
            "ON o.setmeal_id = s.id " +
            "GROUP BY o.setmeal_id")
    List<Map> getSetmealProportion();

    @Select("SELECT " +
            "count(tm.id) AS memberCounts,concat( rp.fyear, \"-\", rp.fmonth ) AS yearAndMonth " +
            "FROM " +
            "t_member tm,rpt_date rp " +
            "WHERE " +
            "tm.REGTIME = rp.fdate " +
            "AND " +
            "rp.fdate BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY " +
            "rp.fmonth")
    List<Map> getMemberCountByTime(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT " +
            "tm.sex as name,count(tm.id) as value " +
            "FROM " +
            "t_member tm " +
            "GROUP BY tm.sex;")
    List<Map> getMemberCountsBySex();


    //查询所有用户的生日
    @Select("SELECT COUNT(*) " +
            "from t_member " +
            "WHERE BIRTHDAY " +
            "BETWEEN #{beginDate} AND #{endDate}")
    Long findMemberCount(@Param("beginDate") String beginDate,@Param("endDate") String endDate );

}
